package com.kaifamiao.www.XML.do0929;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;

import java.io.IOException;
import java.io.InputStream;
import java.net.DatagramPacket;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class Test {
    public static void main(String[] args) {
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("Stu.xml");
        List<Student> list = praseXML(inputStream);
        System.out.println(insert(list));
    }
    private static String url;
    private static String user;
    private static String password;

    static {
        InputStream inputStream = Test.class.getClassLoader().getResourceAsStream("db.properties");
        Properties prop = new Properties();
        try {
            prop.load(inputStream);
            url = prop.getProperty("url");
            user= prop.getProperty("user");
            password=prop.getProperty("password");
            Class.forName(prop.getProperty("driver"));
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static int insert(List<Student> list){
        int count = 0;
        String sql = "insert into stu(`id`,`name`,`nick`,`gender`,`age`) values(?,?,?,?,?)";
        try (
                Connection connection = DriverManager.getConnection(url,user,password);
                PreparedStatement preparedStatement = connection.prepareStatement(sql);
                ){

            for (Student student : list){
                preparedStatement.setObject(1,student.getId());
                preparedStatement.setObject(2,student.getName());
                preparedStatement.setObject(3,student.getNick());
                preparedStatement.setObject(4,student.getGender());
                preparedStatement.setObject(5,student.getAge());
                count+=preparedStatement.executeUpdate();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return count;
    }
    public static List<Student> praseXML(InputStream in){
        SAXReader saxReader = new SAXReader();
        try {
            Document document =saxReader.read(in);
            Element rootElement = document.getRootElement();
            Element students = rootElement.element("students");
            List<Element> list = students.elements();
//            System.out.println("编号\t姓名\t昵称\t性别\t年龄");
            List<Student> studentList = new ArrayList<>();
            for (Element student :list){
                Element id = student.element("id");
                Element name = student.element("name");
                String nick = name.attributeValue("nick");
                Element gender = student.element("gender");
                Element age = student.element("age");
//                System.out.println(id.getTextTrim()+"\t"+name.getTextTrim()+"\t"+nick+"\t"+gender.getTextTrim()+"\t"+age.getTextTrim());
            Student stu = new Student();
                stu.setId(Integer.parseInt(id.getTextTrim()));
                stu.setName(name.getTextTrim());
                stu.setNick(nick);
                stu.setGender(gender.getTextTrim());
                stu.setAge(Integer.parseInt(age.getTextTrim()));
                studentList.add(stu);
            }
            return studentList;
        } catch (DocumentException e) {
            e.printStackTrace();
        }


        return null;
    }
}
